using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.SLXNK
{
	
	public enum StringSearchWhat
	{
		ALL_WORD = 0,
		ANY_WORD = 1,
		EXACT = 2
	}

	public struct HMDSearchInfo
	{
		public string MaDonVi;
		public string TenHang;
		public StringSearchWhat SWTenHang;
		public string MaLoaiHinh;
		public string MaHaiQuan;
		public DateTime TuNgay;
		public DateTime DenNgay;
		public decimal TGKB_VND;
		public byte TGKB_Compare;
	}

	/// <summary>
	/// Summary description for HangMauDich.
	/// </summary>
	public class HangMauDich
	{
		public static DataSet SearchByToKhaiKey(int soTK, string maLoaiHinh, string maHaiQuan, int namDK)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return SearchByToKhaiKey(connectionString, soTK, maLoaiHinh, maHaiQuan, namDK);
		}

		public static DataSet SearchByToKhaiKey(string connectionString, int soTK, string maLoaiHinh, string maHaiQuan, int namDK)
		{
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT ");
            query.Append("HMD.SoTK, ");
            query.Append("HMD.Ma_LH, ");
            query.Append("HMD.Ma_HQ, ");
            query.Append("HMD.NamDK, ");
            query.Append("HMD.STTHang, ");
            query.Append("HMD.Ma_HangKB AS MaHS, ");
            query.Append("HMD.Ma_Hang, ");
            query.Append("HMD.Ma_Phu, ");
            query.Append("HMD.Ten_Hang AS TenHang, ");
            query.Append("HMD.DinhMuc, ");
            query.Append("HMD.Nuoc_XX, ");
            query.Append("HMD.Ma_DVT, ");
            query.Append("HMD.Luong, ");
            query.Append("HMD.DGia_KB, ");
            query.Append("HMD.DGia_TT, ");
            query.Append("HMD.Ma_DG, ");
            query.Append("HMD.TriGia_KB, ");
            query.Append("HMD.TriGia_TT, ");
            query.Append("HMD.TGKB_VND, ");
            query.Append("HMD.LoaiTSXNK, ");
            query.Append("HMD.TS_XNK, ");
            query.Append("HMD.TS_TTDB, ");
            query.Append("HMD.TS_VAT, ");
            query.Append("HMD.Thue_XNK, ");
            query.Append("HMD.Thue_TTDB, ");
            query.Append("HMD.Thue_VAT, ");
            query.Append("HMD.Phu_Thu, ");
            query.Append("HMD.MienThue, ");
            query.Append("HMD.TL_QuyDoi, ");
            query.Append("HMD.Ma_ThKe, ");
            query.Append("HMD.Ma_DVTQD, ");
            query.Append("HMD.LuongQD, ");
            query.Append("HMD.TGKB_USD, ");
            query.Append("HMD.Choxuly, ");
            query.Append("HMD.TyLe_ThuKhac, ");
            query.Append("HMD.TriGia_ThuKhac, ");

            query.Append("N.Ten_Nuoc AS TenNuoc_XX, ");
            query.Append("DVT.Ten_DVT ");

            query.Append("FROM ");
            query.Append("dbo.DHANGMD HMD INNER JOIN dbo.SNUOC N ON HMD.Nuoc_XX = N.Ma_Nuoc ");
            query.Append("INNER JOIN dbo.SDVT DVT ON HMD.Ma_DVT = DVT.Ma_DVT ");

            query.Append("WHERE ");
            query.Append("SoTK = @SoToKhai AND ");
            query.Append("MA_LH = @MaLoaiHinh AND ");
            query.Append("MA_HQ = @MaHaiQuan AND ");
            query.Append("NamDK = @NamDangKy ");

            query.Append("ORDER BY ");
            query.Append("HMD.STTHang ");

            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@SoToKhai", SqlDbType.Int);
            parameters[0].Value = soTK;

            parameters[1] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char, 5);
            parameters[1].Value = maLoaiHinh;

            parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[2].Value = maHaiQuan;

            parameters[3] = new SqlParameter("@NamDangKy", SqlDbType.Int);
            parameters[3].Value = namDK;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                row["TenHang"] = FontConverter.TCVN2Unicode(row["TenHang"].ToString());
            }
		    return ds;
		}
		//-----------------------------------------------------------------------------------------

		public static DataSet Search(HMDSearchInfo hmdSearchInfo)
		{			
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", hmdSearchInfo.MaHaiQuan);
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT ");
			query.Append("(CONVERT(varchar(10), HMD.SoTK) + '-' + HMD.Ma_LH + '-' + RTRIM(HMD.MA_HQ) + '-' + CONVERT(varchar(10), HMD.NamDK) + '-' + CONVERT(varchar(10), HMD.STTHang)) AS Khoa, ");
			query.Append("HMD.SoTK, ");
			query.Append("TK.Ngay_DK, ");
			query.Append("LH.Ten_LH, ");
			query.Append("HQ.Ma_HQ, ");
			query.Append("HQ.Ten_HQ, ");
			query.Append("HMD.STTHang, ");
			query.Append("HMD.Ten_Hang, ");
			query.Append("HMD.Ma_HangKB, ");
			query.Append("N.Ten_Nuoc AS TenNuoc_XX, ");
			query.Append("DVT.Ten_DVT, ");
			query.Append("HMD.Luong, ");
			query.Append("HMD.TriGia_KB, ");
			query.Append("HMD.TGKB_VND, ");
			query.Append("HMD.Ma_Hang, ");
			query.Append("HMD.DGia_TT, ");
			query.Append("HMD.TriGia_TT, ");
			query.Append("HMD.Ma_DG, ");
			query.Append("HMD.LoaiTSXNK, ");
			query.Append("HMD.TS_XNK, ");
			query.Append("HMD.Thue_XNK, ");
			query.Append("HMD.TS_TTDB, ");
			query.Append("HMD.Thue_TTDB, ");
			query.Append("HMD.TS_VAT, ");
			query.Append("HMD.Thue_VAT, ");
			query.Append("HMD.Phu_Thu, ");
			query.Append("HMD.MienThue ");
			query.Append("FROM DHANGMD HMD ");
			query.Append("INNER JOIN SNUOC N ON HMD.Nuoc_XX = N.Ma_Nuoc ");
			query.Append("INNER JOIN SDVT DVT ON HMD.Ma_DVT = DVT.Ma_DVT ");			
			query.Append("INNER JOIN DTOKHAIMD TK ON HMD.SoTK = TK.SoTK AND HMD.Ma_LH = TK.Ma_LH AND HMD.Ma_HQ = TK.Ma_HQ AND HMD.NamDK = TK.NamDK ");
			query.Append("INNER JOIN SHAIQUAN HQ ON TK.Ma_HQ = HQ.Ma_HQ ");
			query.Append("INNER JOIN SLHINHMD LH ON TK.Ma_LH = LH.Ma_LH ");
			query.Append("WHERE ");
			// Ten hang.
			if (hmdSearchInfo.TenHang.Trim().Length > 0)
			{
				string[] words;
				hmdSearchInfo.TenHang = FontConverter.Unicode2TCVN(hmdSearchInfo.TenHang);
				switch (hmdSearchInfo.SWTenHang)
				{
					case StringSearchWhat.ALL_WORD:
						words = hmdSearchInfo.TenHang.Split(' ');
						foreach (string word in words)
						{
							query.Append(string.Format("HMD.Ten_Hang LIKE '%{0}%' AND ", word));
						}
						// Remove last AND in sql query.
						//query.Remove(query.Length - 5, 5);
						break;
					case StringSearchWhat.ANY_WORD:
						words = hmdSearchInfo.TenHang.Split(' ');
						foreach (string word in words)
						{
							query.Append(string.Format("HMD.Ten_Hang LIKE '%{0}%' OR ", word));
						}
						// Remove last OR in sql query.
						query.Remove(query.Length - 4, 4);
						query.Append(" AND ");
						break;
					case StringSearchWhat.EXACT:
						query.Append(string.Format("HMD.Ten_Hang LIKE '%{0}%' AND ", hmdSearchInfo.TenHang));
						break;
				}				
			}
			//			if (soToKhai > 0)
			//				query.Append(string.Format("SoTK = {0} AND ", soToKhai));
			//
			// Loai hinh.
			if (hmdSearchInfo.MaLoaiHinh != "   ")
				query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", hmdSearchInfo.MaLoaiHinh));
			//
			// Hai quan mo to khai.
			if (hmdSearchInfo.MaHaiQuan != "   ")
				query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", hmdSearchInfo.MaHaiQuan));
			//
			//			// Nam dang ky.
			//			if (namDangKy > 1900)
			//				query.Append(string.Format("TK.NamDK = '{0}' AND ", namDangKy));
			//
			// Tu ngay -> den ngay.
			if (hmdSearchInfo.TuNgay.Year > 1900 && hmdSearchInfo.DenNgay.Year > 1900)
				query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");
			//
			//			// Ma don vi.
			//			if (maDonVi.Length > 0)
			//				query.Append(string.Format("TK.Ma_DV LIKE '{0}' AND ", maDonVi));
			//			
			//			// Tri gia khai bao.
			switch (hmdSearchInfo.TGKB_Compare)
			{
				case 0:
					query.Append("HMD.TGKB_VND >= @TGKB AND ");
					break;
				case 1:
					query.Append("HMD.TGKB_VND <= @TGKB AND ");
					break;
				case 2:
					query.Append("HMD.TGKB_VND = @TGKB AND ");
					break;
				default:
					query.Append("HMD.TGKB_VND >= 0 AND ");
					break;
			}
			//				
			//			// Nuoc xuat khau.
			//			if (nuocXuatKhau != "XXX")
			//				query.Append(string.Format("TK.Nuoc_XK LIKE '{0}' AND ", nuocXuatKhau));
			//
			//			// Nuoc nhap khau.
			//			if (nuocXuatKhau != "XXX")
			//				query.Append(string.Format("TK.Nuoc_NK LIKE '{0}' AND ", nuocNhapKhau));
			//
			//			// Cua khau xuat, nhap.
			//			if (maCuaKhau != "XXX")
			//				query.Append(string.Format("TK.Ma_CK LIKE '{0}'", maCuaKhau));
			
			// Prevent error.
			query.Append("'1' = '1'");

			query.Append(" ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

			SqlParameter[] parameters = new SqlParameter[3];
			parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
			parameters[0].Value = hmdSearchInfo.TuNgay;

			parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
			parameters[1].Value = hmdSearchInfo.DenNgay;

			parameters[2] = new SqlParameter("@TGKB", SqlDbType.Decimal);
			parameters[2].Value = hmdSearchInfo.TGKB_VND;

			try
			{				
				return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message + "----" + query.ToString());
			}
		}
	}
}
